﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WM.MySqlTool
{
    public class DBSchemaMgr
    {
        string connectionString = "server=127.0.0.1;port=3306;User Id=root;Password=123456;database=soil;Connect Timeout=60;Treat Tiny As Boolean=False";
        public List<string> Tables { get; private set; }
        public DBSchemaMgr()
        {
            Tables = GetTables();
        }
        /// <summary>
        /// 获取全部的表明
        /// </summary>
        /// <returns></returns>
        private List<string> GetTables()
        {
            List<string> list = new List<string>();
            using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
            {
                conn.Open();
                DataTable table = conn.GetSchema("TABLES");
                foreach (DataRow row in table.Rows)
                {
                    DataTable sheetTable = GetSchemaTable(conn, row);

                    list.Add(row["TABLE_NAME"].ToString());
                }
            }
            return list;
        }

        internal List<string> GetColumns(string tableName)
        {
            List<string> list = new List<string>();
            using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
            {
                conn.Open();
                MySqlCommand com = conn.CreateCommand();
                com.CommandType = CommandType.Text;
                com.CommandText = "Select * From " + tableName;
                IDataReader reader = com.ExecuteReader(CommandBehavior.SchemaOnly);
                DataTable table = reader.GetSchemaTable();
                foreach (DataRow itemRow in table.Rows)
                {
                    list.Add(itemRow["ColumnName"].ToString());
                }
                com.Dispose();
                reader.Close();
            }
            return list;
        }

        internal string CreateTableAppendSql(string tableName)
        {
            using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
            {
                conn.Open();
                MySqlCommand com = conn.CreateCommand();
                com.CommandType = CommandType.Text;
                com.CommandText = "Select * From " + tableName;
                IDataReader reader = com.ExecuteReader(CommandBehavior.SchemaOnly);
                DataTable table = reader.GetSchemaTable();
                List<string> list = new List<string>();
                string tableSchema = string.Empty;
                foreach (DataRow itemRow in table.Rows)
                {
                    list.Add(itemRow["ColumnName"].ToString());
                    if (string.IsNullOrEmpty(tableSchema))
                        tableSchema = itemRow["BaseSchemaName"].ToString();
                }
                reader.Close();
                Dictionary<string, string> dict = dict = GetFielComment(tableName, tableSchema, conn);
                com.Dispose();

                return CreateTableAppendSql(tableName, table, dict);
            }
        }

        private string CreateTableSelectSql(string tableName, DataTable table, Dictionary<string, string> dict)
        {
            StringBuilder codeSeticon = new StringBuilder();
            codeSeticon.Append("string sqlFind = @\"SELECT");
            StringBuilder codeColSeticon = new StringBuilder();
            foreach (DataRow row in table.Rows)
            {
                string colName = row["ColumnName"].ToString();
                codeSeticon.AppendFormat(" {0},", colName.ToUpper());
                codeColSeticon.AppendFormat("model.{0} = itemRow[\"{1}\"].ToString();", colName, colName).AppendLine();
            }
            codeSeticon.AppendFormat(" FROM {0} WHERE GUID = @P_GUID \";", tableName.ToUpper()).AppendLine();
            codeSeticon.AppendFormat("{0} model = new {0}();", tableName.ToUpper());
            codeSeticon.AppendLine("List<MySqlParameter> paras = new List<MySqlParameter>();");
            codeSeticon.AppendLine("paras.Add(new MySqlParameter(\"P_GUID\", MySqlDbType.VarChar) { Value = mGUID });");
            codeSeticon.AppendLine("using (MySqlDataReader itemRow = _db.ExecuteReader(sqlFind, paras.ToArray()))");
            codeSeticon.AppendLine("{");
            codeSeticon.AppendLine("if (itemRow.Read())");
            codeSeticon.AppendLine("{");

            codeSeticon.Append(codeColSeticon);

            codeSeticon.AppendLine("}");
            codeSeticon.AppendLine("}");
            return codeSeticon.ToString();
        }

        internal string CreateTableSqlSelect(string tableName)
        {
            using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
            {
                conn.Open();
                MySqlCommand com = conn.CreateCommand();
                com.CommandType = CommandType.Text;
                com.CommandText = "Select * From " + tableName;
                IDataReader reader = com.ExecuteReader(CommandBehavior.SchemaOnly);
                DataTable table = reader.GetSchemaTable();
                List<string> list = new List<string>();
                string tableSchema = string.Empty;
                foreach (DataRow itemRow in table.Rows)
                {
                    list.Add(itemRow["ColumnName"].ToString());
                    if (string.IsNullOrEmpty(tableSchema))
                        tableSchema = itemRow["BaseSchemaName"].ToString();
                }
                reader.Close();
                Dictionary<string, string> dict = dict = GetFielComment(tableName, tableSchema, conn);
                com.Dispose();

                return CreateTableSelectSql(tableName, table, dict);
            }
        }

        internal string CreateTableHTML(string tableName)
        {

            StringBuilder sbContent = new StringBuilder();
            using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
            {
                conn.Open();
                MySqlCommand com = conn.CreateCommand();
                com.CommandType = CommandType.Text;
                com.CommandText = "Select * From " + tableName;
                IDataReader reader = com.ExecuteReader(CommandBehavior.SchemaOnly);
                DataTable table = reader.GetSchemaTable();
                List<string> list = new List<string>();
                string tableSchema = string.Empty;
                foreach (DataRow itemRow in table.Rows)
                {
                    list.Add(itemRow["ColumnName"].ToString());
                    if (string.IsNullOrEmpty(tableSchema))
                        tableSchema = itemRow["BaseSchemaName"].ToString();
                }
                reader.Close();
                Dictionary<string, string> dict = dict = GetFielComment(tableName, tableSchema, conn);
                com.Dispose();

                return CreateTableHTML(tableName, table, dict);
            }
        }

        private string CreateTableHTML(string tableName, DataTable table, Dictionary<string, string> dict)
        {
            StringBuilder codeSeticon = new StringBuilder();
             
            foreach (DataRow row in table.Rows)
            {
                string colName = row["ColumnName"].ToString();
                if (false)
                {
                    codeSeticon.AppendLine("<div class=\"form-group row\">");
                    codeSeticon.AppendFormat("  <label for=\"{0}\" class=\"col-sm-2 col-form-label\">{1}</label>", colName, dict[colName]).AppendLine();
                    codeSeticon.AppendLine("    <div class=\"col-sm-4\">");
                    codeSeticon.AppendFormat("      <input type =\"text\" class=\"form-control\" name=\"{0}\" value=\"@Model.{0}\">", colName).AppendLine();
                    codeSeticon.AppendLine("    </div>");
                    codeSeticon.AppendLine("</div>");
                }
                else
                {

                    codeSeticon.AppendFormat("  <label for=\"{0}\" class=\"col-sm-2 col-form-label\">{1}</label>", colName, dict[colName]).AppendLine();

                    codeSeticon.AppendFormat("  <input type =\"text\" class=\"form-control\" readonly name=\"{0}\" value=\"@item.{0}\">", colName).AppendLine();

                    //codeSeticon.AppendFormat("  <label for=\"{0}\" class=\"col-sm-2 col-form-label  text-sm-right\">@item.{0}</label>", colName).AppendLine();
                }


            }


            return codeSeticon.ToString();
        }

        internal string CreateTableUpdateSql(string tableName)
        {
            StringBuilder sbContent = new StringBuilder();
            using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
            {
                conn.Open();
                MySqlCommand com = conn.CreateCommand();
                com.CommandType = CommandType.Text;
                com.CommandText = "Select * From " + tableName;
                IDataReader reader = com.ExecuteReader(CommandBehavior.SchemaOnly);
                DataTable table = reader.GetSchemaTable();
                List<string> list = new List<string>();
                string tableSchema = string.Empty;
                foreach (DataRow itemRow in table.Rows)
                {
                    list.Add(itemRow["ColumnName"].ToString());
                    if (string.IsNullOrEmpty(tableSchema))
                        tableSchema = itemRow["BaseSchemaName"].ToString();
                }
                reader.Close();
                Dictionary<string, string> dict = dict = GetFielComment(tableName, tableSchema, conn);
                com.Dispose();

                return CreateTablUpdateSql(tableName, table, dict);
            }
        }

        private string CreateTablUpdateSql(string tableName, DataTable table, Dictionary<string, string> dict)
        {
            StringBuilder codeSeticon = new StringBuilder();
            codeSeticon.Append("string sqlUpdate = @\"").Append("UPDATE ").AppendLine(tableName.ToUpper()).Append(" SET ");
            StringBuilder sbSqlParas = new StringBuilder();
            foreach (DataRow row in table.Rows)
            {
                string colName = row["ColumnName"].ToString();
                codeSeticon.AppendFormat(" {0} = @P_{0},", colName.ToUpper()).AppendLine();
                sbSqlParas.AppendFormat("paras.Add(new MySqlParameter(\"P_").Append(colName.ToUpper()).Append("\", MySqlDbType.VarChar)").Append(" { Value = model.").Append(colName).Append(" });").AppendLine();
            }
            codeSeticon.AppendLine("WHERE 1=1 \";");
            codeSeticon.AppendLine("List<MySqlParameter> paras = new List<MySqlParameter>();");
            codeSeticon.AppendLine(sbSqlParas.ToString());


            codeSeticon.AppendLine("return _db.ExecuteNonQuery(sqlUpdate, paras.ToArray()) > 0;");

            return codeSeticon.ToString();
        }

        private string CreateTableAppendSql(string tableName, DataTable table, Dictionary<string, string> dict)
        {

            StringBuilder sbColumns = new StringBuilder();
            StringBuilder sbParas = new StringBuilder();
            StringBuilder sbSqlParas = new StringBuilder();

            foreach (DataRow row in table.Rows)
            {
                string colName = row["ColumnName"].ToString();
                sbColumns.AppendFormat(" {0},", colName.ToUpper());
                sbParas.AppendFormat(" @P_{0},", colName.ToUpper());
                sbSqlParas.AppendFormat("paras.Add(new MySqlParameter(\"P_").Append(colName.ToUpper()).Append("\", MySqlDbType.VarChar) { Value = model.").Append(colName).AppendLine(" });");
            }

            StringBuilder codeSeticon = new StringBuilder();
            codeSeticon.Append("string sqlAppend = @\"").Append("INSERT INTO ").Append(tableName.ToUpper())
                .Append("(")
                .Append(sbColumns.ToString().Trim(','))
                .Append(" ) VALUES (")
                .Append(sbParas.ToString().Trim(','))
                .AppendLine(")\"");
            codeSeticon.AppendLine("List<MySqlParameter> paras = new List<MySqlParameter>();");
            codeSeticon.AppendLine(sbSqlParas.ToString());
            codeSeticon.AppendLine("return _db.ExecuteNonQuery(sqlAppend, paras.ToArray()) > 0;");

            return codeSeticon.ToString();
        }

        /// <summary>
        /// 根据表明生成内容
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        internal string CreateTableClass(string tableName)
        {
            StringBuilder sbContent = new StringBuilder();
            using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
            {
                conn.Open();
                MySqlCommand com = conn.CreateCommand();
                com.CommandType = CommandType.Text;
                com.CommandText = "Select * From " + tableName;
                IDataReader reader = com.ExecuteReader(CommandBehavior.SchemaOnly);
                DataTable table = reader.GetSchemaTable();
                List<string> list = new List<string>();
                string tableSchema = string.Empty;
                foreach (DataRow itemRow in table.Rows)
                {
                    list.Add(itemRow["ColumnName"].ToString());
                    if (string.IsNullOrEmpty(tableSchema))
                        tableSchema = itemRow["BaseSchemaName"].ToString();
                }
                reader.Close();
                Dictionary<string, string> dict = dict = GetFielComment(tableName, tableSchema, conn);
                com.Dispose();

                return CreateTableClass(tableName, table, dict);
            }
        }

        private string CreateTableClass(string tableName, DataTable table, Dictionary<string, string> dict)
        {
            StringBuilder sw = new StringBuilder();

            //sw.AppendLine("using System;");
            //sw.AppendLine("using System.Text;");
            //sw.AppendLine("using System.Collections.Generic; ");
            //sw.AppendLine("using System.Data;");
            //sw.AppendLine("namespace AEBell.DBManager");
            //sw.AppendLine("{");
            sw.AppendLine("\t[Serializable]");
            sw.AppendLine("\tpublic class " + tableName);
            sw.AppendLine("\t{");
            foreach (DataRow row in table.Rows)
            {
                //tableName = row["BaseTableName"].ToString();
                string colName = row["ColumnName"].ToString();
                string schemaName = row["BaseSchemaName"].ToString();

                sw.AppendLine("\t\t/// <summary>");
                sw.AppendLine("\t\t/// " + dict[colName]);
                sw.AppendLine("\t\t/// </summary>");

                Type info = row["DataType"] as Type;
                string declear = info.Name;
                if (declear.ToUpper() == "SBYTE")   //为了适应动软。
                    declear = "Byte";
                bool isNull = (bool)row["AllowDBNull"];
                if (isNull && info.BaseType.Name == "ValueType")
                    declear += "?";

                sw.AppendLine("\t\tpublic " + declear + " " + colName.Substring(0, 1).ToUpper() + colName.Substring(1));
                sw.AppendLine("\t\t{");
                sw.AppendLine("\t\t\tget;");
                sw.AppendLine("\t\t\tset;");
                sw.AppendLine("\t\t}");
            }
            sw.AppendLine("\t}");

            //sw.AppendLine("}");

            return sw.ToString();
        }

        /// <summary>
        /// 获取列备注
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="tableSchema"></param>
        /// <param name="conn"></param>
        /// <returns></returns>
        private Dictionary<string, string> GetFielComment(string tableName, string tableSchema, MySqlConnection conn)
        {
            Dictionary<string, string> dict = new Dictionary<string, string>();

            string sqlCommandText = "Select COLUMN_NAME,DATA_TYPE, COLUMN_COMMENT FROM  INFORMATION_SCHEMA.COLUMNS where table_name ='"
                + tableName + "' AND table_schema = '" + tableSchema + "'";
            MySqlCommand command = new MySqlCommand(sqlCommandText, conn);
            using (IDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    string colName = reader.GetString(0);
                    dict[colName] = reader.GetString(2);
                }
                reader.Close();
            }
            return dict;
        }

        private void CreateModel()
        {
            MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
            conn.Open();
            DataTable table = conn.GetSchema("TABLES");
            foreach (DataRow row in table.Rows)
            {
                DataTable sheetTable = GetSchemaTable(conn, row);
                string tName = row["TABLE_NAME"].ToString();
                CreateEntityFile(conn, tName, sheetTable);
            }
            conn.Close();
        }

        /// <summary>
        /// 获取列明集合
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="row"></param>
        /// <returns></returns>
        private DataTable GetSchemaTable(MySqlConnection conn, DataRow row)
        {
            string sheetName = row["TABLE_Name"].ToString();
            MySqlCommand com = conn.CreateCommand();
            com.CommandType = CommandType.Text;
            com.CommandText = "Select * From " + sheetName;
            IDataReader reader = com.ExecuteReader(CommandBehavior.SchemaOnly);
            DataTable table = reader.GetSchemaTable();
            com.Dispose();
            reader.Close();
            return table;
        }
        /// <summary>
        /// 创建实体文件
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="tName"></param>
        /// <param name="table"></param>
        private void CreateEntityFile(MySqlConnection conn, string tName, DataTable table)
        {
            //定义生成文件的路径
            string tableName = tName;
            string schemaName = "";
            string colName = "";
            string path = @"F:\IPBroadcastModel\Model1";
            if (!Directory.Exists(path))
                Directory.CreateDirectory(path);

            MySqlCommand com = conn.CreateCommand();
            com.CommandType = CommandType.Text;
            Dictionary<string, string> dict = new Dictionary<string, string>();
            bool isGetComment = false;

            //写文件
            string clsName = "Ety" + tableName.Substring(3, 1).ToUpper() + tableName.Substring(4);
            string fileName = clsName + ".cs";
            string fullName = Path.Combine(path, fileName);

            using (FileStream fs = new FileStream(fullName, FileMode.Create))
            {
                StreamWriter sw = new StreamWriter(fs, Encoding.UTF8);
                sw.WriteLine("using System;");
                sw.WriteLine("using System.Text;");
                sw.WriteLine("using System.Collections.Generic; ");
                sw.WriteLine("using System.Data;");
                sw.WriteLine("namespace AEBell.DBManager");
                sw.WriteLine("{");
                sw.WriteLine("\t[Serializable]");
                sw.WriteLine("\tpublic class " + clsName);
                sw.WriteLine("\t{");
                foreach (DataRow row in table.Rows)
                {
                    //tableName = row["BaseTableName"].ToString();
                    colName = row["ColumnName"].ToString();
                    schemaName = row["BaseSchemaName"].ToString();
                    if (!isGetComment)
                    {
                        isGetComment = true;
                        GetFielComment(tableName, schemaName, com, dict);
                    }

                    sw.WriteLine("\t\t/// <summary>");
                    sw.WriteLine("\t\t/// " + dict[colName]);
                    sw.WriteLine("\t\t/// </summary>");

                    Type info = row["DataType"] as Type;
                    string declear = info.Name;
                    if (declear.ToUpper() == "SBYTE")   //为了适应动软。
                        declear = "Byte";
                    bool isNull = (bool)row["AllowDBNull"];
                    if (isNull && info.BaseType.Name == "ValueType")
                        declear += "?";

                    sw.WriteLine("\t\tpublic " + declear + " " + colName.Substring(0, 1).ToUpper() + colName.Substring(1));
                    sw.WriteLine("\t\t{");
                    sw.WriteLine("\t\t\tget;");
                    sw.WriteLine("\t\t\tset;");
                    sw.WriteLine("\t\t}");
                }
                sw.WriteLine("\t}");
                sw.WriteLine("}");
                sw.Close();
            }
        }
        /// <summary>
        /// 获取列备注
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="schemaName"></param>
        /// <param name="com"></param>
        /// <param name="dict"></param>
        private static void GetFielComment(string tableName, string schemaName, MySqlCommand com, Dictionary<string, string> dict)
        {
            string comment = "";
            com.CommandText = "Select COLUMN_NAME,DATA_TYPE, COLUMN_COMMENT FROM  INFORMATION_SCHEMA.COLUMNS where table_name ='"
                + tableName + "' AND table_schema = '" + schemaName + "'"; // AND column_name LIKE '" + colName + "'";
            IDataReader reader = com.ExecuteReader();
            while (reader.Read())
            {
                string colName = reader.GetString(0);
                comment = reader.GetString(2);
                dict[colName] = comment;
            }
            reader.Close();
        }
    }
}
